﻿using SmartProcess.Core.Commons.Tracing;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MMSA.Services.SharedServices
{
    /// <summary>
    /// Parser for XLS or XLSX Files
    /// </summary>
    public static class SpreadsheetParser
    {

        public static void GetDataFromExcelSpreadsheet(this DataTable table, string filePath, string fileExtension)
        {
            TracingService tracing = new TracingService("MMSA.Services");
            string traceServicio = string.Empty;
            try
            {
                OleDbConnection oledbConn = null;

                if (fileExtension == "xls")
                {
                    traceServicio = "Abriendo conexion para extension xls";
                    oledbConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"");
                }
                else if (fileExtension == "xlsx")
                {
                    traceServicio = "Abriendo conexion para extension xlsx";
                    oledbConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + filePath + "; Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';");
                }

                var adapter = new OleDbDataAdapter(@"SELECT * FROM [Hoja1$]", oledbConn);
                var ds = new DataSet();

                adapter.Fill(table);
            }
            catch (Exception exc)
            {
                if (exc != null)
                    tracing.ErrorFormat("MMSA.Services.SharedServices.SpreadsheetParser ==> TRACE:{0}, ==> ERROR:{1}, ==> STACKTRACE:{2}", traceServicio, exc.Message, exc.StackTrace);
            }

        }

    }
}
